package com.fernandomdo.ServiceAdministrator.databaseInterface;

import static android.provider.BaseColumns._ID;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Calendar;
import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.content.CursorLoader;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;
import android.util.Log;

import com.fernandomdo.ServiceAdministrator.Publisher.PublisherListItem;
import com.fernandomdo.ServiceAdministrator.Territory;
import com.fernandomdo.ServiceAdministrator.Util;

/**
 * This class is an interface to the database file. Make sure that you don't handle the database anywhere else but here!
 * @author fernando_mdo
 * @see {@link CursorLoader}
 *
 */
public class DatabaseInterface {
	public static String TAG = "DatabaseInterface";
		
	// Private variables
	public SQLiteDatabase readableDB; 
	public SQLiteDatabase writableDB;
	public DBHelper databaseHelper;
	
	public DatabaseInterface(Context context) {
		Log.d(TAG, "new DatabaseInterface created");
		
		// Setup the database access
		databaseHelper = new DBHelper(context);
	}
	
	// Some useful classes
	/////////////////////////////////////////////////////////////////////////////////
	/**
    *
    * This class helps open, create, and upgrade the database file.
    */
	public class DBHelper extends SQLiteOpenHelper {
		// Constructors
		public DBHelper(Context context) {
			super(context, DatabaseConstants.DATABASE_NAME, null, DatabaseConstants.DATABASE_VERSION);
		}
				
		@Override
		public void onCreate(SQLiteDatabase db) {
			onUpgrade(db, 0, DatabaseConstants.DATABASE_VERSION);
		}
		
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// Database will be created for the first time
			if(oldVersion < 2) {
				// Create the table of territories
				db.execSQL("CREATE TABLE " + DatabaseConstants.TERRITORY_TABLE + "(" 
						+ _ID + " INTEGER PRIMARY KEY, " 
						+ DatabaseConstants.STATUS + " INTEGER NOT NULL, "
						+ DatabaseConstants.TERRITORY + " TEXT NOT NULL);");
				
				// Create the territory info table
				db.execSQL("CREATE TABLE " + DatabaseConstants.TERRITORY_INFO_TABLE + "(" 
						+ _ID + " INTEGER PRIMARY KEY, "
						+ DatabaseConstants.TERRITORY_ID + " INTEGER NOT NULL, "
						+ DatabaseConstants.DESCRIPTION + " TEXT NOT NULL, "
						+ DatabaseConstants.CURRENT_BORROWER + " TEXT, " 
						+ DatabaseConstants.LAST_BORROWER + " TEXT, " 
						+ DatabaseConstants.TIMES_BORROWED_MONTH + " INTEGER, "
						+ DatabaseConstants.TIMES_BORROWED_YEAR + " INTEGER, "
						+ DatabaseConstants.DATE_IN + " INTEGER, "
						+ DatabaseConstants.DATE_OUT + " INTEGER);");
				
				// Create the table of publishers				
				db.execSQL("CREATE TABLE " + DatabaseConstants.PUBLISHER_TABLE + "(" 
						+ _ID + " INTEGER PRIMARY KEY, "
						+ DatabaseConstants.PUBLISHER + " TEXT NOT NULL);");	
				
				// Create the publisher info table
				db.execSQL("CREATE TABLE " + DatabaseConstants.PUBLISHER_INFO_TABLE + "(" 
						+ _ID + " INTEGER PRIMARY KEY, "
						+ DatabaseConstants.PUBLISHER_ID + " INTEGER NOT NULL, "
						+ DatabaseConstants.CURRENT_IN_POSSESION + " INTEGER, " 
						+ DatabaseConstants.LAST_IN_POSSESION + " INTEGER, " 
						+ DatabaseConstants.AMOUNT_BORROWED + " INTEGER, "
						+ DatabaseConstants.LAST_DATE_OUT + " INTEGER, "
						+ DatabaseConstants.LAST_DATE_IN + " INTEGER);");	
			}
			
		}		
	}
	
	// Other methods
	////////////////////////////////////////////////////////////////////////////////
	
	
	public boolean addTerritory(String territory, String description) {
		writableDB = databaseHelper.getWritableDatabase();
		
		ContentValues values = new ContentValues();
		values.put(DatabaseConstants.TERRITORY, territory);
		values.put(DatabaseConstants.STATUS, Territory.AVAILABLE);
		
		long territoryId;
		if((territoryId = writableDB.insert(DatabaseConstants.TERRITORY_TABLE, null, values)) != -1) {
			//Also add an entry into the info table
			values.clear();
			values.put(DatabaseConstants.TERRITORY_ID, territoryId);
			values.put(DatabaseConstants.DESCRIPTION, description);
			values.put(DatabaseConstants.CURRENT_BORROWER, -1);
			values.put(DatabaseConstants.LAST_BORROWER, -1);
			values.put(DatabaseConstants.TIMES_BORROWED_MONTH, -1);
			values.put(DatabaseConstants.TIMES_BORROWED_YEAR, -1);
			values.put(DatabaseConstants.DATE_IN, 0);
			values.put(DatabaseConstants.DATE_OUT, 0);
			
			//db.execSQL("DELETE FROM territory WHERE rowid=?", new Long[] { mTerritoryForDelete });
	  		//db.execSQL("DELETE FROM person WHERE (SELECT territory_id FROM door WHERE door.ROWID=person.door_id LIMIT 1)=?", new Long[] { mTerritoryForDelete });
	  		//db.execSQL("DELETE FROM door WHERE territory_id=?", new Long[] { mTerritoryForDelete });				  		
	  		//db.execSQL("DELETE FROM visit WHERE territory_id=?", new Long[] { mTerritoryForDelete });
			
			/*
			
			String name = Util.dbFetchString(db, "SELECT name FROM territory WHERE ROWID=?", new String[] {mDialogItemId.toString()});
			Cursor rs = db.rawQuery("SELECT rowid _id,name,strftime('%s',started), strftime('%s', finished) FROM territory ORDER BY started DESC", new String[] {});
			Cursor rs = db.rawQuery("SELECT territory_id,color1 FROM door ORDER BY territory_id,group_id,order_num ASC", new String[] {});
			*/
			/*
			writableDB.execSQL("INSERT INTO " + DatabaseConstants.TERRITORY_INFO_TABLE 
					+ " (" + DatabaseConstants.TERRITORY_ID + ", " + DatabaseConstants.DESCRIPTION + ")"
					+ " VALUES(" + territoryId + ", \"" + description + "\")");
			*/
			
			long rowid = writableDB.insert(DatabaseConstants.TERRITORY_INFO_TABLE, null, values);			
			writableDB.close();
			return true;			
		}
		else {
			writableDB.close();
			return false;
		}	
	}

	public boolean addPublisher(String publisher) {
		writableDB = databaseHelper.getWritableDatabase();
		
		ContentValues values = new ContentValues();
		values.put(DatabaseConstants.PUBLISHER, publisher);
		
		long publisherId;
		if((publisherId = writableDB.insert(DatabaseConstants.PUBLISHER_TABLE, null, values)) != -1) {
			//Also add an entry into the info table
			values.clear();
			values.put(DatabaseConstants.PUBLISHER_ID, publisherId);
			values.put(DatabaseConstants.CURRENT_IN_POSSESION, -1);
			values.put(DatabaseConstants.LAST_IN_POSSESION, -1);
			values.put(DatabaseConstants.AMOUNT_BORROWED, -1);
			values.put(DatabaseConstants.LAST_DATE_IN, 0);
			values.put(DatabaseConstants.LAST_DATE_OUT, 0);
			
			long rowid = writableDB.insert(DatabaseConstants.PUBLISHER_INFO_TABLE, null, values);
			Log.d(TAG, "new row with id: " + rowid);
			
			writableDB.close();
			return true;			
		}
		else {
			writableDB.close();
			return false;
		}	
	}
	
	public boolean deleteTerritory(long territoryId) {
		writableDB = databaseHelper.getWritableDatabase();
		
		//First delete the associated data in the info table
		int infoCount = writableDB.delete(DatabaseConstants.TERRITORY_INFO_TABLE,DatabaseConstants.TERRITORY_ID + "=" + territoryId, null);
		
		//Now delete the entry from the main table
		int entryCount = writableDB.delete(DatabaseConstants.TERRITORY_TABLE, BaseColumns._ID+"="+territoryId, null);
		
		Log.d(TAG, infoCount + " entries deleted from infoTable and " + entryCount + " entries deleted from main table");
		
		return true;
	}

	public boolean deletePublisher(long publisherId) {
		writableDB = databaseHelper.getWritableDatabase();
		
		//First delete the associated data in the info table
		int infoCount = writableDB.delete(DatabaseConstants.PUBLISHER_INFO_TABLE, DatabaseConstants.PUBLISHER_ID + "=" + publisherId, null);
		
		//Now delete the entry from the main table
		int entryCount = writableDB.delete(DatabaseConstants.PUBLISHER_TABLE, BaseColumns._ID+"="+publisherId, null);
		
		Log.d(TAG, infoCount + " entries deleted from infoTable and " + entryCount + " entries deleted from main table");
		
		return true;
	}
	
	/**
	 * <b>Summary</b>
	 * <p> Checks out a territory to the given publisher
	 * @param territoryId
	 * @param publisher
	 * @param date
	 * @return
	 */
	public boolean checkOutTerritory(long territoryId, PublisherListItem publisher, Date date) {
		ContentValues territoryValues = new ContentValues();
		ContentValues territoryInfoValues = new ContentValues();
		ContentValues publisherInfoValues = new ContentValues();
		Cursor oldTerritoryValue;
		Cursor oldTerritoryInfoValue;
		Cursor oldPublisherInfoValue;
		Date currentDate = Calendar.getInstance().getTime();
				
		oldTerritoryValue = readableDB.query(DatabaseConstants.TERRITORY_TABLE,  
				new String[]{ BaseColumns._ID, DatabaseConstants.TERRITORY, DatabaseConstants.STATUS}, 
				BaseColumns._ID+"=?", 
				new String[]{String.valueOf(territoryId)}, 
				null, null, null);
		
		oldTerritoryInfoValue = readableDB.query(DatabaseConstants.TERRITORY_INFO_TABLE,  
				new String[]{ DatabaseConstants.TERRITORY_ID, DatabaseConstants.CURRENT_BORROWER, DatabaseConstants.TIMES_BORROWED_MONTH, DatabaseConstants.TIMES_BORROWED_YEAR}, 
				DatabaseConstants.TERRITORY_ID+"=?", 
				new String[]{String.valueOf(territoryId)}, 
				null, null, null);
		
		oldPublisherInfoValue = readableDB.query(DatabaseConstants.PUBLISHER_INFO_TABLE,  
				new String[]{ DatabaseConstants.PUBLISHER_ID, DatabaseConstants.CURRENT_IN_POSSESION, DatabaseConstants.AMOUNT_BORROWED }, 
				DatabaseConstants.PUBLISHER_ID+"=?", 
				new String[]{String.valueOf(publisher.id)}, 
				null, null, null);
		
		if(!oldTerritoryValue.moveToFirst() || !oldTerritoryInfoValue.moveToFirst() || !oldPublisherInfoValue.moveToFirst()) {
			//Couldn't get the requested data
			return false;
		}
		
		if(Util.cursorFetchBool(oldTerritoryValue, DatabaseConstants.STATUS) == Territory.NOT_AVAILABLE) {
			//The territory can't be checked out because it is already checked out
			return false;
		}
		
		//We are now ready to update the rows in the database
		//Fill in the values that will be updated in the Territories table
		territoryValues.put(DatabaseConstants.STATUS, Territory.NOT_AVAILABLE);

		//It is the first day of the month, so reset the monthly counts
		if(Calendar.getInstance().get(Calendar.DAY_OF_MONTH) == 1) {
			//territoryInfoValues.put(DatabaseConstants.TIMES_BORROWED_MONTH, 1);
		}
		
		//It is the first day of the year, so reset the yearly counts
		if(Calendar.getInstance().get(Calendar.DAY_OF_YEAR) == 1) {
			//territoryInfoValues.put(DatabaseConstants.TIMES_BORROWED_MONTH, 1);
		}
		
		//Fill in the values that will be updated in the Territory Info table
		territoryInfoValues.put(DatabaseConstants.LAST_BORROWER, Util.cursorFetchString(oldTerritoryInfoValue, DatabaseConstants.CURRENT_BORROWER));
		territoryInfoValues.put(DatabaseConstants.CURRENT_BORROWER, publisher.name);
		
		//Fill in the values that will be updated in the Publisher Info table
		publisherInfoValues.put(DatabaseConstants.LAST_IN_POSSESION, Util.cursorFetchLong(oldPublisherInfoValue, DatabaseConstants.CURRENT_IN_POSSESION));
		publisherInfoValues.put(DatabaseConstants.CURRENT_IN_POSSESION, territoryId);
		
		int numUpdated = readableDB.update(DatabaseConstants.TERRITORY_TABLE, territoryValues, BaseColumns._ID+"=?", new String[]{ String.valueOf(territoryId)});
		numUpdated = readableDB.update(DatabaseConstants.TERRITORY_INFO_TABLE, territoryInfoValues, DatabaseConstants.TERRITORY_ID+"=?", new String[]{String.valueOf(territoryId)});
		numUpdated = readableDB.update(DatabaseConstants.PUBLISHER_INFO_TABLE, publisherInfoValues, DatabaseConstants.PUBLISHER_ID+"=?", new String[]{String.valueOf(publisher.id)});
		if(numUpdated > 0) {
			return true;
		}
		else {
			return false;
		}
	}
		
	/**
	 * 
	 * @param territoryNumber
	 * @param date
	 * @return boolean
	 */
	public boolean checkInTerritory(long territoryId, Date date) {
		ContentValues territoryValues = new ContentValues();
		ContentValues territoryInfoValues = new ContentValues();
		ContentValues publisherInfoValues = new ContentValues();
		Cursor oldTerritoryValue;
		Cursor oldTerritoryInfoValue;
		Cursor oldPublisherInfoValue;
		Date currentDate = Calendar.getInstance().getTime();
		
		oldTerritoryValue = readableDB.query(DatabaseConstants.TERRITORY_TABLE,  
				new String[]{ BaseColumns._ID, DatabaseConstants.TERRITORY, DatabaseConstants.STATUS}, 
				BaseColumns._ID+"=?", 
				new String[]{String.valueOf(territoryId)}, 
				null, null, null);
		
		if(!oldTerritoryValue.moveToFirst()) {
			//Couldn't get the requested data
			return false;
		}
		
		if(Util.cursorFetchBool(oldTerritoryValue, DatabaseConstants.STATUS) == Territory.AVAILABLE) {
			//The territory can't be checked out because it hasn't been checked out
			return false;
		}
		
		
		//We are now ready to update the rows in the database
		//Fill in the values that will be updated in the Territories table
		territoryValues.put(DatabaseConstants.STATUS, Territory.AVAILABLE);

		int numUpdated = readableDB.update(DatabaseConstants.TERRITORY_TABLE, territoryValues, BaseColumns._ID+"=?", new String[]{ String.valueOf(territoryId)});
		Log.d(TAG, "Territory udpate affected " + numUpdated + " rows");
		
		if(numUpdated > 0) {
			return true;
		}
		else {
			return false;
		}
	}
	
	
	/**
	 * Method to export the database file to the SD card
	 * @param {@link String} exportFileName
	 * @throws IOException
	 */
	public void exportDatabase(String exportFileName) throws IOException 
	{
		OutputStream databaseOutputStream = new FileOutputStream("/mnt/sdcard/ServiceAdminsitrator/databases/" + exportFileName);
        InputStream databaseInputStream = new FileInputStream("/data/data/com.fernandomdo/databases/"+ DatabaseConstants.DATABASE_NAME);

        byte[] buffer = new byte[1];
        int length;
        while ( (length = databaseInputStream.read(buffer)) > 0 ) {
                databaseOutputStream.write(buffer);
                //Log.w("Bytes: ", ((Integer)length).toString());
                //Log.w("value", buffer.toString());
        }

        databaseOutputStream.flush();
        databaseOutputStream.close();
        databaseInputStream.close();
     } 

	/**
	 * Method to import a copy of the database from the SD card
	 * @param {@link String} importFileName
	 * @throws IOException
	 */
	public void importDatabase(String importFileName) throws IOException
	{
		InputStream databaseInputStream = new FileInputStream("/mnt/sdcard/ServiceAdministrator/databases/" + importFileName);
	    OutputStream databaseOutputStream = new FileOutputStream("/data/data/com.fernandomdo/databases/" + DatabaseConstants.DATABASE_NAME);
	        
        byte[] buffer = new byte[1];
        int length;
        while ( (length = databaseInputStream.read(buffer)) > 0 ) {
            databaseOutputStream.write(buffer);
                //Log.w("Bytes: ", ((Integer)length).toString());
                //Log.w("value", buffer.toString());
        }

        databaseOutputStream.flush();
        databaseOutputStream.close();
        databaseInputStream.close();
	}

	
/////////////////////////////////////////////////////////////////////////////
	
	
	
/////////////////////////////////////////////////////////////////////////////////
	
	/**
	 * Provides a simple interface to a list of constants that will be used to access the database information
	 * Make sure you update the version number any time you make changes to any part of the database!
	 * 
	 * @author fernando_mdo
	 * @version
	 * @see {@link DatabaseInterface}
 	 * @since 1.0
 	 **/
	public interface DatabaseConstants {
		public static final int DATABASE_VERSION = 1;
		public static final String DATABASE_NAME = "territoryDatabase.db";	
		
		// Territories Table
		public static final String TERRITORY_TABLE = "territories";
		public static final String TERRITORY = "territory";
		
		// Territory Info Table
		public static final String TERRITORY_INFO_TABLE = "territory_info";
		public static final String TERRITORY_ID = "territory_id";
		public static final String DESCRIPTION = "description";
		public static final String STATUS = "status";
		public static final String CURRENT_BORROWER = "current_borrower";
		public static final String LAST_BORROWER = "last_borrower";
		public static final String TIMES_BORROWED_MONTH = "borrowed_during_month";
		public static final String TIMES_BORROWED_YEAR = "borrowed_during_year";
		public static final String DATE_IN = "datein";
		public static final String DATE_OUT = "dateout";
		
		
		// Publishers Table
		public static final String PUBLISHER_TABLE = "publishers";
		public static final String PUBLISHER = "publisher";
		
		// Publisher Info Table
		public static final String PUBLISHER_INFO_TABLE = "publisher_info";
		public static final String PUBLISHER_ID = "publisher_id";
		public static final String CURRENT_IN_POSSESION = "current_in_possesion";
		public static final String LAST_IN_POSSESION = "last_in_possesion";
		public static final String AMOUNT_BORROWED = "amount_borrowed_during_month";
		public static final String LAST_DATE_OUT = "last_date_territory_borrowed";
		public static final String LAST_DATE_IN = "last_date_territory_returned";
	}

}